'use strict';

const BaseService = require('./base');

class CollectionService extends BaseService {
  get _model() {
    return 'Collection';
  }

  async isExist({ houseId, cid, uid, paranoid = true }) {
    const where = this.formatWhere({ exact: { houseId, cid, uid } });
    return this._isExist({ where, paranoid });
  }

  async create({ houseId, cid, uid }) {
    return this._create({
      houseId, cid, uid,
    });
  }

  async restore({ houseId, cid, uid }) {
    const where = this.formatWhere({ exact: { houseId, cid, uid } });
    return this._restore({ where });
  }

  async destory({ houseId, cid, uid }) {
    const where = this.formatWhere({ exact: { houseId, cid, uid } });
    return this._destory({ where });
  }

  async findList({ uid, pageIndex, pageSize, paranoid }) {
    const { app } = this;
    const where = this.formatWhere({ exact: { uid } });
    const include = [
      {
        model: app.model.House,
        attributes: [ 'name', 'description' ],
        include: [
          {
            model: app.model.HouseInfo,
            attributes: [ 'pictures' ],
          },
        ],
      },
      {
        model: app.model.Comment,
        attributes: [ 'title', 'content', 'pictures' ],
      },
    ];
    return this._findList({ pageIndex, pageSize, where, include, paranoid });
  }

  async statisticalCollectionHouse({ uid }) {
    const sql = 'SELECT `house`.`id`, `house`.`name`, COUNT(*) FROM `house` LEFT JOIN `collection` on `collection`.`houseId` = `house`.`id` WHERE `house`.`oid` = ? GROUP BY `house`.`name` ORDER BY `COUNT(*)` DESC LIMIT 5';
    return await this.querySelect(sql, [ uid ]);
  }

  async statisticalCollectionComment({ uid }) {
    const sql = 'SELECT `comment`.`id`, `comment`.`title`, COUNT(*) FROM `comment` LEFT JOIN `collection` on `collection`.`cid` = `comment`.`id` WHERE `comment`.`uid` = ? GROUP BY `comment`.`title` ORDER BY `COUNT(*)` DESC LIMIT 5';
    return await this.querySelect(sql, [ uid ]);
  }

  async statisticalCollectionHouseAll() {
    const sql = 'SELECT `house`.`id`, `house`.`name`, COUNT(*) FROM `house` LEFT JOIN `collection` on `collection`.`houseId` = `house`.`id` WHERE `house`.`status` = 4 GROUP BY `house`.`name` ORDER BY `COUNT(*)` DESC LIMIT 5';
    return await this.querySelect(sql);
  }

  async statisticalCollectionCommentAll() {
    const sql = 'SELECT `comment`.`id`, `comment`.`title`, COUNT(*) FROM `comment` LEFT JOIN `collection` on `collection`.`cid` = `comment`.`id` WHERE `comment`.`status` = 2 GROUP BY `comment`.`title` ORDER BY `COUNT(*)` DESC LIMIT 5';
    return await this.querySelect(sql);
  }

  // 获取某用户收藏从而推断出地区喜好
  async collectionAreaLike({ uid }) {
    const sql = 'SELECT `house`.`areaId`,COUNT(*) as num FROM `collection` RIGHT JOIN `house` ON `house`.`id` = `collection`.`houseId` WHERE `collection`.`uid` = ? GROUP BY `house`.`areaId` ORDER BY num DESC';
    return await this.querySelect(sql, [ uid ]);
  }

  // 获取用户喜欢的价格区间
  async collectionRentLike({ uid }) {
    const sql = `SELECT 
            SUM(CASE WHEN \`house\`.\`rent\` BETWEEN 0 AND 1000 THEN 1 ELSE 0 END) AS "0-1000",
            SUM(CASE WHEN \`house\`.\`rent\` BETWEEN 1001 AND 2000 THEN 1 ELSE 0 END) AS "1000-2000",
            SUM(CASE WHEN \`house\`.\`rent\` BETWEEN 2001 AND 3000 THEN 1 ELSE 0 END) AS "2000-3000",
            SUM(CASE WHEN \`house\`.\`rent\` BETWEEN 3001 AND 4000 THEN 1 ELSE 0 END) AS "3000-4000",
            SUM(CASE WHEN \`house\`.\`rent\` BETWEEN 4001 AND 5000 THEN 1 ELSE 0 END) AS "4000-5000",
            SUM(CASE WHEN \`house\`.\`rent\` > 5000 THEN 1 ELSE 0 END) AS "5000+"
                FROM \`collection\` RIGHT JOIN \`house\` ON \`house\`.\`id\` = \`collection\`.\`houseId\` WHERE \`collection\`.\`uid\` = ?`;
    return await this.querySelect(sql, [ uid ]);
  }
}

module.exports = CollectionService;
